package com.pushtalk.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import com.pushtalk.MainPush;
import com.pushtalk.SqlConnection;
import com.pushtalk.entity.BussApp;
import com.pushtalk.entity.BussNotify;

/**
 * DAO数据操作
 * 需要改进的是InsertNewNotify需要改为事务处理
 * @author wx
 *
 */
public class BussDAO {

	private static String GET_ALL_BussApp="SELECT id,DbUrl,BussType,ListenSQL,IDField,SubjectField,UsernameField,LinkURLField,dbUser,dbPassword,dbDriver,JoinID,JoinIDType FROM tkBussAppListener";
	private static String GET_ALL_Notify="SELECT id,BussType,Subject,Username,LinkURL FROM tkBussNotify";
	private static String INSERT_BussNotify="INSERT INTO tkBussNotify (id ,BussType ,Subject ,Username ,LinkURL) VALUES (?,?,?,?,?)";
	private static String INSERT_BussShortURL="INSERT INTO tkBussShortURL (shortURL ,longURL ) VALUES (?,?)";
	static Logger logger  =  Logger.getLogger(BussDAO.class);
	
	public static List<BussApp> getAllBussApp(String runningEnvironment){
		
		List<BussApp> BussAppList=new ArrayList<BussApp>();
		Connection conn = SqlConnection.getPushConnection();
		Statement st=null;
		ResultSet rs=null;
		try {
			st=conn.createStatement();
			rs=st.executeQuery(GET_ALL_BussApp +" where RunningEnvironment ='"+runningEnvironment+"'");
			while(rs.next()){
				String id=rs.getString("id");
				String dbUrl=rs.getString("DbUrl");
				String bussType=rs.getString("BussType");
				String listenSQL=rs.getString("ListenSQL");
				String iDField=rs.getString("IDField");
				String subjectField=rs.getString("SubjectField");
				String usernameField=rs.getString("UsernameField");
				String linkURLField=rs.getString("LinkURLField");
				String dbUser=rs.getString("dbUser");
				String dbPassword=rs.getString("dbPassword");
				String dbDriver=rs.getString("dbDriver");
				String joinID=rs.getString("JoinID");
				String joinIDType=rs.getString("JoinIDType");
								
				BussApp BussApp=new BussApp();
				BussApp.setId(id);
				BussApp.setBussType(bussType);
				BussApp.setDbUrl(dbUrl);
				BussApp.setIDField(iDField);
				BussApp.setLinkURLField(linkURLField);
				BussApp.setListenSQL(listenSQL);
				BussApp.setSubjectField(subjectField);
				BussApp.setUsernameField(usernameField);
				BussApp.setDbPassword(dbPassword);
				BussApp.setDbUser(dbUser);
				BussApp.setDbDriver(dbDriver);
				BussApp.setJoinID(joinID);
				BussApp.setJoinIDType(joinIDType);
				BussAppList.add(BussApp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error(e);
		}finally{
			try {
				st.close();
				rs.close();
				conn.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}
		return BussAppList;
	}
	
	public static List<BussNotify> getAllNotify(){
		
		List<BussNotify> BussNotifyList=new ArrayList<BussNotify>();
		Connection conn = SqlConnection.getPushConnection();
		Statement st=null;
		ResultSet rs=null;
		try {
			st=conn.createStatement();
			rs=st.executeQuery(GET_ALL_Notify);
			while(rs.next()){
				String id=rs.getString("id");
				String bussType=rs.getString("BussType");
				String subject=rs.getString("Subject");
				String username=rs.getString("Username");
				String linkURL=rs.getString("LinkURL");

				BussNotify bussNotify=new BussNotify();
				bussNotify.setId(id);
				bussNotify.setBussType(bussType);
				bussNotify.setSubject(subject);
				bussNotify.setUsername(username);
				bussNotify.setLinkURL(linkURL);
				BussNotifyList.add(bussNotify);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error(e);
		}finally{
			try {
				st.close();
				rs.close();
				conn.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}
		return BussNotifyList;
	}
	
	public static String InsertNewNotify(BussApp BussApp) {

		String sqlQuery = BussApp.getListenSQL();//监听SQL脚本执行
		Connection conn = SqlConnection.GetDBConnection(BussApp.getDbDriver(),
				BussApp.getDbUrl(),
				BussApp.getDbUser(),
				BussApp.getDbPassword());
		if (conn == null)
			return "";

		Statement stm = null;
		ResultSet rs = null;
		try {
			stm = conn.createStatement();
			
			StringBuilder sql = new StringBuilder();
			String maxid = GetMaxID(BussApp.getBussType());
			
			//判断关联ID类型，字符型需要在MAXID判断时加单引号
			if (BussApp.getJoinIDType().equals("String"))
			{
			  sql.append(sqlQuery);
			  if (!maxid.equals("")){
				  sql.append("   and "+ BussApp.getJoinID()+" > '" + maxid );  				  
			  }

			  sql.append("'  order by "+ BussApp.getJoinID());
			}
			else //int型
			{
			  sql.append(sqlQuery);
			  if (!maxid.equals("")){
				  sql.append("   and "+ BussApp.getJoinID()+" > " + GetMaxID(BussApp.getBussType()));  
			  }
			  sql.append("  order by "+ BussApp.getJoinID());  
			}
			//System.out.println(sql);
			rs = stm.executeQuery(sql.toString());

			//提醒信息
			List<BussNotify> bns =new ArrayList<BussNotify>();
			String maxID="";
			while (rs.next()) {
				BussNotify bn = new BussNotify();
				bn.setBussType(BussApp.getBussType());
				bn.setId(rs.getString(BussApp.getIDField()));		
				if (rs.getString(BussApp.getLinkURLField())!=null && rs.getString(BussApp.getLinkURLField()).length()>0)
					bn.setLinkURL(rs.getString(BussApp.getLinkURLField()));
				else
					bn.setLinkURL("");
				bn.setSubject(rs.getString(BussApp.getSubjectField()));
				bn.setUsername(rs.getString(BussApp.getUsernameField()));
				//因为之前的查询根据JoinID排序了，所以最后获取的ID就是本次更新的最大ID
				maxID = rs.getString(BussApp.getIDField());
				bns.add(bn);
			}
			
			//根据rs，添加提醒信息
			BussNotifyAdd(bns);
			
			//更新最大ID
			if (!maxID.equals("") || maxID.length()>1){
				MaxIDUpdate(maxID,BussApp.getBussType());
			}
			
			return maxID;//返回最大ID

		} catch (SQLException e) {
			e.printStackTrace();
			logger.error(e);
		} finally {
			try {
				rs.close();
			} catch (SQLException ex) {
			}
			try {
				stm.close();
			} catch (SQLException ex) {
			}
			try {
				conn.close();
			} catch (SQLException ex) {

			}
		}
		return "";
	}

	public static String  GetMaxID(String bussType) {
		Connection conn = SqlConnection.getPushConnection();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement("SELECT id FROM tkBussMaxID where BussType = '"+bussType+"'");
			rs=ps.executeQuery();
			while(rs.next()){
				String id=rs.getString("id");
				return id;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error(e);
		}finally{
			try {
				ps.close();
				rs.close();
				conn.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}
		return "";
	}

	public static void MaxIDUpdate(String maxID, String bussType) {
		
		Connection conn = SqlConnection.getPushConnection();
		String sql = "UPDATE tkBussMaxID set id='"+maxID+"' where BussType='"+ bussType +"'";
		Statement stm = null;
		try {
				stm = conn.createStatement();
				stm.executeUpdate(sql);
			} catch (SQLException e) {
				e.printStackTrace();
				logger.error(e);
			} finally {
				try {
					stm.close();
				} catch (SQLException ex) {
				}
				try {
					conn.close();
				} catch (SQLException ex) {

				}
			}
		
	}
	
	public static void MaxIDInit(String bussType) {
		
		Connection conn = SqlConnection.getPushConnection();
		String sql = "INSERT INTO tkBussMaxID (id ,BussType) values('"+0+"','"+bussType+"') ";
		Statement stm = null;
		try {
				stm = conn.createStatement();
				stm.executeUpdate(sql);
			} catch (SQLException e) {
				e.printStackTrace();
				logger.error(e);
			} finally {
				try {
					stm.close();
				} catch (SQLException ex) {
				}
				try {
					conn.close();
				} catch (SQLException ex) {

				}
			}
		
	}
	
	public static void DeleteAllNotify(String maxid)
	{
		Connection conn = SqlConnection.getPushConnection();
		String sql = "Delete from tkBussNotify where id<='"+ maxid +"'";
		Statement stm = null;
		try {
				stm = conn.createStatement();
				stm.executeUpdate(sql);
			} catch (SQLException e) {
				e.printStackTrace();
				logger.error(e);
			} finally {
				try {
					stm.close();
				} catch (SQLException ex) {
				}
				try {
					conn.close();
				} catch (SQLException ex) {

				}
			}
		
	}
	
	@SuppressWarnings("finally")
	public static int DeleteAllNotify()
	{
		int count = 0;
		Connection conn = SqlConnection.getPushConnection();
		String sql = "Delete from tkBussNotify";
		Statement stm = null;
		try {
				stm = conn.createStatement();
				count = stm.executeUpdate(sql);
			} catch (SQLException e) {
				e.printStackTrace();
				logger.error(e);
			} finally {
				try {
					stm.close();
				} catch (SQLException ex) {
				}
				try {
					conn.close();
				} catch (SQLException ex) {

				}
				return count;
			}
		
	}
	
	public static void DeleteNotify(String id,String busstype)
	{
		Connection conn = SqlConnection.getPushConnection();
		String sql = "Delete from tkBussNotify where id='"+ id +"' and BussType='"+ busstype +"'";
		Statement stm = null;
		try {
				stm = conn.createStatement();
				stm.executeUpdate(sql);
			} catch (SQLException ex) {
				ex.printStackTrace();
			} finally {
				try {
					stm.close();
				} catch (SQLException ex) {
				}
				try {
					conn.close();
				} catch (SQLException ex) {

				}
			}
		
	}

	private static void BussNotifyAdd(List<BussNotify> bns) {
		
		Connection conn = SqlConnection.getPushConnection();
		PreparedStatement ps=null;
		try {
			ps=conn.prepareStatement(INSERT_BussNotify);
			for(BussNotify bn:bns){
				ps.setString(1, bn.getId());
				ps.setString(2, bn.getBussType());
				ps.setString(3, bn.getSubject());
				ps.setString(4, bn.getUsername());
				ps.setString(5, bn.getLinkURL());				
				ps.addBatch();
			}
			ps.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error(e);
		}finally{
			try {
				ps.close();
				conn.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}
	}
	
	
	public static void ShortURLAdd(String shortURL,String longURL) {
		Connection conn = SqlConnection.getPushConnection();
		PreparedStatement ps=null;
		try {
			ps=conn.prepareStatement(INSERT_BussShortURL);
				ps.setString(1,shortURL);
				ps.setString(2,longURL);			
				ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error(e);
		}finally{
			try {
				ps.close();
				conn.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		}
	}
}
